PostgreSQL 高可用 keepalived需求

3 配置SSH 互信

Keepalived 需要配置 SSH 互信。

4 准备数据库环境(node1 )

4.1 创建探测表sr_delay

1、创建数据库 Keepalived。
2、创建表探测表 sr_delay，被 Keepalived 探测。
3、刷新 sr_delay 表的last_alive字段为当前探测时间。
4、这张表用来判断主备延迟情况，数据库故障切换时会用到这张表。

psql -U postgres -d testdb
CREATE USER keepalived PASSWORD 'keepalived' CONNECTION LIMIT 4 ;

CREATE DATABASE keepalived OWNER keepalived;
\c keepalived keepalived
CREATE TABLE sr_delay(id int4, last_alive timestamp(0) without time zone);

4.2 配置 pg_hba.conf

后续Keepalived会每隔指定时间探测 PostgreSQL 数据库存活，并且以Keepalived用户登录Keepalived数据库刷新这张表，
所以需要配置所有节点的 pg_hba.conf ，增加如下内容：

host    keepalived    keepalived        192.168.10.173/32      scram-sha-256
host    keepalived    keepalived        192.168.10.174/32       scram-sha-256
host    keepalived    keepalived        192.168.10.175/32       scram-sha-256

随后 pg_ctl reload 操作使配置生效。

4.3 创建函数和触发器

1、表 sr_delay 只允许写入一条记录。
2、此条记录不允许删。
3、可以通过触发器实现，如下所示：

4.3.1 创建函数cannot_delete

\c keepalived keepalived
CREATE FUNCTION cannot_delete () 
RETURNS trigger 
LANGUAGE plpgsql AS $ 
BEGIN 
RAISE EXCEPTION 'Table sr_delay Can Not Delete !';
 END; 
$;

4.3.2 创建 delete 触发器。

CREATE TRIGGER trigger_sr_delay_del 
BEFORE DELETE ON sr_delay 
FOR EACH ROW EXECUTE PROCEDURE cannot_delete() ; 

4.3.3 创建 TRUNCATE 触发器

CREATE TRIGGER trigger_sr_delay_tru 
BEFORE TRUNCATE ON sr_delay FOR STATEMENT EXECUTE PROCEDURE cannot_delete() ;

4.3.4 插入初始数据

 INSERT INTO sr_delay VALUES(1,now()) ;